The power of Power Query
In this article, I would like to introduce a tool that I use to speed up my daily work - it is called Power Query. My former manager, Nathan, explained Power Query to me when I started my first role at UTAS, and it has been a crucial item in my toolbox since then.
In simple terms, Power Query is a feature in Excel that helps users to process a large amount of raw data into the desired form. The best part is that the steps in the "Query" are saved, so the next batch of data (same tabular format) can be processed within just a few clicks. The user interface is intuitive and well laid out so it is quite easy to pick up. It is substantially easier to learn than VBA. Power Query is like VBA but for dummies. Sounds too good to be true? Let me give an example.
I inherited a report from my predecessor, where the monthly transactions needed to be sent to a program manager for categorising. I then put that data into the previous month's report, updating the source for the pivot table (summarising by category), and then checking the report sheet to see if the numbers matched the pivot table. This process was prone to errors. I could omit transactions when transferring data to the report. I could update the source for the pivot table incorrectly. If I accidentally changed/deleted a number in the raw data set, the report would be inaccurate. Moreover, the whole process took anywhere between 20-30 minutes to complete, depending on the number of mistakes.
With Power Query, I easily addressed the issues above. I set up a sequence of steps where the monthly data was pulled into a separate workbook in a summarised format. I only needed to create an xlookup formula in the report tab, linking to the summarised data. I did not need to worry about transferring data and updating the pivot table. Every month, I refreshed the summarised table to automatically include the new data. It took less than 30 seconds. The data was kept separate from the report, hence it was safe from unintentional changes.
This is a fairly straightforward example. There are many more ways Power Query can help to boost productivity, here are a few:
- Add column by example: As the name suggests, you can add a column by giving examples of what the data should be, based on the selected column(s). For example, extracting the six digits after the first "-" in a product code or quickly assigning a code based on the product category. I find this one particularly useful when I am lazy to write a nested IF function.
Photo Credit: Microsoft @ Microsoft Learn - Pivot/Unpivot data: a use case for this feature is when you have data by month in columns but want the months to be in a single column. You can do so by using the unpivot feature.
.png)
Photo Credit: Microsoft @ Microsoft Learn - Merging data with Joins: when you need to compare two sets of data, the Joins can help to identify which items in one data set quickly are not in the other one.
Photo Credit: Microsoft @ Microsoft Learn - Editable steps: you can change the parameters/conditions in certain steps in the Query without recreating the whole Query.
Photo Credit: Microsoft @ Microsoft Learn - Extracting data from a wide range of sources: Power Query can pull data from a CSV file, a table in Excel, Excel files in a folder, data servers, and websites. You can even get data from your mailbox if you want to accurately calculate the average daily emails received as evidence to support a pay rise request.
Photo Credit: Microsoft @ Microsoft Learn
Even with all the powerful features, there are a few limitations of Power Query that you should be aware of. If you accidentally delete a step in the Query, you may ruin the whole Query. In my experience, it is rather tricky to work backward on the steps that are not editable. You need to learn the M language for this. Also, you can not switch between the current workbook and the Power Query Editor window.
If you want to explore further, I would like to suggest the Excel: Power Query (Get & Transform) course on LinkedIn Learning by Oz du Soleil or the Master Excel Power Query - Beginner to Pro course by Leila Gharani. Both of them are Microsoft Most Valuable Professional (MVP) in Excel.
If you are a new graduate accountant working with a lot of transactions on a daily basis, Power Query will change your life. In case my article has not persuaded you to start learning Power Query, have a look at the video below or click here, and prepare to be mind blown.